/**
 * 
 */
package org.paradise.dms.dao.impl;

import java.util.Date;
import java.util.List;

import org.apache.commons.lang.builder.ReflectionToStringBuilder;
import org.apache.log4j.Logger;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.paradise.dms.pojo.Dormitory;
import org.paradise.dms.pojo.Student;
import org.paradise.dms.pojo.StudentBedLink;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Service;

import com.dheaven.framework.dao.BaseHibernateDao;
import com.dheaven.framework.dao.DaoException;

/**
 * Description: StudentBedLinkDAO
 * 
 * @author kk
 * @version 1.0 Mar 25th, 2009 23:32:59 PM 闫克 （kurtyan777@gmail.com） created
 * @version 1.1 Mar 29th, 2009 00:57:59 AM 闫克 （kurtyan777@gmail.com） updated
 *          重构了insertStudentBedLink方法，为其添加了startintime及endouttime参数
 */
@Service
public class StudentBedLinkDAOImpl extends BaseHibernateDao<StudentBedLink> {
	final private static Logger log = Logger.getLogger(StudentBedLinkDAOImpl.class);

	/**
	 * 
	 * Description: 插入一条床位分配信息 Caution: 此方法功能为 新增学生住宿信息
	 * 应先根据studentid在studentbedlink表中查找 如果结果不为空，说明该学生已经获得宿舍分配，此时应返回错误信息；
	 * 如果结果为空，则继续进行插入操作
	 * 
	 * @Version 1.0 2009-3-26 00:14:49 闫克 （kurtyan777@gmail.com） 创建
	 * @Version 1.1 2009-3-29 22:52:59 闫克 （kurtyan777@gmail.com） 修改 添加了 插入前的
	 *          学生住宿状态判断功能
	 * @param studentid
	 * @param dormitoryid
	 * @return statusMsg
	 */
	@SuppressWarnings("unchecked")
	public String insertStudentBedLink(int studentid, final int dormitoryid) {
		String statusMsg = "";
		List<StudentBedLink> stuList;
		try {
			stuList = this.find("from StudentBedLink where studentid = ?", new Object[] { studentid });
		} catch (DaoException e1) {
			e1.printStackTrace();
			// statusMsg = "Error";
			statusMsg = "查询学生住宿状态失败";
			log.error("DMS_error:查询学生住宿状态失败");
			return statusMsg;
		}
		if (stuList.size() > 0) {
			statusMsg = "该学生已经分配宿舍";
			log.warn("DMS_warn:" + statusMsg);
			return statusMsg;
		}
		StudentBedLink studentbedlink = new StudentBedLink();
		List querylist = (List) this.getHibernateTemplate().execute(new HibernateCallback() {
			public List doInHibernate(Session session) {
				SQLQuery query = (SQLQuery) session.createSQLQuery("{call GetAvailBedByDormId(?)}").addScalar("dormitorybedid", Hibernate.INTEGER);
				query.setInteger(0, dormitoryid);
				return query.list();
			}
		});

		if (querylist.size() == 0) { // 这个宿舍没有可分配的床位了嘛！
			statusMsg = "宿舍床位已满 !";
			log.warn("DMS_warn:" + statusMsg);
			return statusMsg;
		}
		// log.info(query.list().get(0) + "=lshuangj==3/27");
		Integer dbid = (Integer) querylist.get(0);
		studentbedlink.setStudentid(studentid);
		studentbedlink.setDormitoryid(dormitoryid);
		studentbedlink.setDormitorybedid(dbid);

		try {
			this.create(studentbedlink);
			log.info("DMS_info:分配成功");

			// 请勿修改此返回值消息！ service层要根据返回值进行逻辑判断
			return statusMsg; // 如果分配成功，返回值为""
		} catch (DaoException e) {
			statusMsg = "分配失败";
			log.error("DMS_error:" + statusMsg);
			log.error("DMS_error: 失败原因 " + e);
			return statusMsg;
		}
	}

	/**
	 * 
	 * Description: 插入一条床位分配信息 with startintime and endouttime Caution: 此方法功能为
	 * 新增学生住宿信息 应先根据studentid在studentbedlink表中查找
	 * 如果结果不为空，说明该学生已经获得宿舍分配，此时应返回错误信息； 如果结果为空，则继续进行插入操作
	 * 
	 * @Version 1.0 2009-3-28 00:15:49 闫克 （kurtyan777@gmail.com） 创建
	 * @Version 1.1 2009-3-29 22:54:59 闫克 （kurtyan777@gmail.com） 修改 添加了 插入前的
	 *          学生住宿状态判断功能
	 * @param studentid
	 * @param dormitoryid
	 * @param studentbedstartintime
	 * @param studentbedendouttime
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String insertStudentBedLink(int studentid, final int dormitoryid, Date studentbedstartintime, Date studentbedendouttime) {
		String statusMsg = "";
		List<StudentBedLink> stuList;
		try {
			stuList = this.find("from StudentBedLink where studentid = ?", new Object[] { studentid });
		} catch (DaoException e1) {
			e1.printStackTrace();
			statusMsg = "Error";
			log.error("DMS_error:查询学生住宿状态失败");
			return statusMsg;
		}
		if (stuList.size() > 0) {

			// 请勿更改此返回值！！ Action层会用到
			statusMsg = "该学生已经分配宿舍";
			log.warn("DMS_warn:" + statusMsg);
			return statusMsg;
		}
		StudentBedLink studentbedlink = new StudentBedLink();

		List querylist = (List) this.getHibernateTemplate().execute(new HibernateCallback() {
			public List doInHibernate(Session session) {
				SQLQuery query = (SQLQuery) session.createSQLQuery("{call GetAvailBedByDormId(?)}").addScalar("dormitorybedid", Hibernate.INTEGER);
				query.setInteger(0, dormitoryid);
				return query.list();
			}
		});

		if (querylist.size() == 0) { // 这个宿舍没有可分配的床位了嘛！
			statusMsg = "宿舍床位已满 !";
			log.warn("DMS_warn:" + statusMsg);
			return statusMsg;
		}
		// log.info(query.list().get(0) + "=lshuangj==3/27");

		Integer dbid = (Integer) querylist.get(0);
		studentbedlink.setStudentid(studentid);
		studentbedlink.setDormitoryid(dormitoryid);
		studentbedlink.setDormitorybedid(dbid);
		studentbedlink.setStudentbedstartintime(studentbedstartintime);
		studentbedlink.setStudentbedendouttime(studentbedendouttime);

		try {
			this.create(studentbedlink);
			log.info("DMS_info:分配成功");

			// 请勿更改此返回值！！service层会用到
			return statusMsg; // 如果分配成功，返回值为""
		} catch (DaoException e) {
			statusMsg = "分配失败";
			log.error("DMS_error:" + statusMsg);
			log.error("DMS_error: 失败原因 " + e);
			return statusMsg;
		}
	}

	/**
	 * Description: 根据学生id、宿舍id、床位id插入一条分配信息。 用于批量分配
	 * 
	 * @param studentid
	 * @param dormitoryid
	 * @param dormitorybedid
	 * @return Boolean
	 */
	@SuppressWarnings("unchecked")
	public String insertStudentBedLink(final String studentid, String dormitoryid, String dormitorybedid, Date startintime, Date endouttime) {
		List<StudentBedLink> stuList;
		String statusMsg = "";
		// 判断学生的床位信息是否存在
		try {
			stuList = (List<StudentBedLink>) this.find("from StudentBedLink where studentid = ?", new Object[] { Integer.parseInt(studentid) });
		} catch (DaoException e1) {
			e1.printStackTrace();

			// 切勿更改此return消息值！ service层需根据其进行逻辑判断
			statusMsg = "Error";
			return statusMsg;
		}
		if (stuList.size() > 0) {// 如果这个学生已经住宿了，那么我们把他的预分配标记设为-1吧 by paradise
			statusMsg = (String) this.getHibernateTemplate().execute(new HibernateCallback() {
				public String doInHibernate(Session session) {
					SQLQuery query = (SQLQuery) session.createSQLQuery("update student s set s.studentpreasignedid=? where s.studentid=?");
					query.setInteger(0, -1);
					query.setInteger(1, Integer.parseInt(studentid));
					if (query.executeUpdate() > 0) {
						// 切勿更改此return消息值！ service层需根据其进行逻辑判断
						return "delPreAssignedID";
					}
					return "Error";
				}
			});
			return statusMsg;
		}

		StudentBedLink studentbedlink = new StudentBedLink();
		studentbedlink.setStudentid(Integer.parseInt(studentid));
		studentbedlink.setDormitoryid(Integer.parseInt(dormitoryid));
		studentbedlink.setDormitorybedid(Integer.parseInt(dormitorybedid));
		studentbedlink.setAccomodationstatusid(0);
		studentbedlink.setStudentbedstartintime(startintime);
		studentbedlink.setStudentbedendouttime(endouttime);
		try {
			this.create(studentbedlink);

			// 切勿更改此return消息值！ service层需根据其进行逻辑判断
			statusMsg = "inserted";
			return statusMsg;
		} catch (DaoException e) {
			e.printStackTrace();
			statusMsg = "Error";
			return statusMsg;

		}
	}

	/**
	 * to be updated: 加入住宿时间功能 Description: 更新一条床位分配信息 用于 学生与空床调换 Caution:
	 * 此方法功能为 更新学生住宿信息，所以应先根据studentid在studentbedlink表里查找一下
	 * 如果结果为空，说明该学生尚未获得分配宿舍，此时应返回错误信息 如果结果不为空，再对该结果进行更新操作
	 * 
	 * @Version 1.0 2009-3-26 00:15:50 闫克 （kurtyan777@gmail.com） 创建
	 * @Version 1.1 2009-3-29 22:52:59 闫克 （kurtyan777@gmail.com） 修改 添加了 更新前的
	 *          学生住宿状态判断功能
	 * @param studentid
	 * @param dormitoryid
	 * @return statusMsg
	 */
	@SuppressWarnings("unchecked")
	public String updateStudentBedLink(final int studentid, final int dormitoryid) {
		String statusMsg = "";
		List<StudentBedLink> stuList;
		try {
			stuList = this.find("from StudentBedLink where studentid = ?", new Object[] { studentid });
		} catch (DaoException e1) {
			e1.printStackTrace();
			statusMsg = "Error";
			log.error("DMS_error:查询学生住宿状态失败");
			return statusMsg;
		}
		if ((stuList == null) || (stuList.size() == 0)) {
			statusMsg = "该学生尚未分配宿舍";
			log.warn("DMS_warn:" + statusMsg);
			return statusMsg;
		}

		
		//statusMsg为取得到得dormitorybedid 下文中会用到
		statusMsg = (String) this.getHibernateTemplate().execute(new HibernateCallback() {
			public String doInHibernate(Session session) {
				SQLQuery query = (SQLQuery) session.createSQLQuery("{call GetAvailBedByDormId(?)}").addScalar("dormitorybedid", Hibernate.INTEGER);
				query.setInteger(0, dormitoryid);

				if (query.list().size() == 0) {
					// 请勿修改此返回值！！
					return "宿舍床位已满";
				}

				// Object类型转成String类型的方法
				// 请勿修改此返回值！！
				return query.list().get(0) + "";
			}
		});

		if ("宿舍床位已满".equals(statusMsg)) {
			log.warn("DMS_warn:" + statusMsg);
			return statusMsg;
		}

		StudentBedLink studentbedlink = (StudentBedLink) stuList.get(0);

		studentbedlink.setStudentid(studentid);
		studentbedlink.setDormitoryid(dormitoryid);
		studentbedlink.setDormitorybedid(Integer.parseInt(statusMsg));
		studentbedlink.setStudentbedstartintime(new Date());

		statusMsg = this.insertAccomodationHistory(studentbedlink.getDormitoryid(), studentbedlink.getDormitorybedid(), studentbedlink.getStudentid(), studentbedlink.getStudentbedstartintime(), new Date());
		log.info("DMS_info:已记录学生" + studentbedlink.getStudentid() + "在宿舍" + studentbedlink.getDormitoryid() + "的历史住宿信息");
		try {
			this.merge(studentbedlink);
			statusMsg += "更新成功";
			log.info("DMS_info:宿舍调换成功");
			return statusMsg; // 如果更新成功，返回值为""
		} catch (DaoException e) {
			e.printStackTrace();
			statusMsg += "分配出现异常";
			log.error("DMS_error:宿舍调换失败");
			log.error("DMS_error:失败原因" + e);
			return statusMsg;
		}

	}

	/**
	 * 
	 * Description: 删除一条床位分配信息
	 * 
	 * @Version1.0 2009-3-26 00:28:50 闫克 （kurtyan777@gmail.com） 创建
	 * @param studentbedlink
	 * @return
	 * @throws DaoException
	 * @throws DaoException
	 */
	public String deleteStudentBedLink(final int studentid) throws DaoException {

		final StudentBedLink studentbedlink = (StudentBedLink) this.find("from StudentBedLink s where s.studentid = ?", new Object[] { studentid }).get(0);

		return (String) this.getHibernateTemplate().execute(new HibernateCallback() {
			public String doInHibernate(Session session) {
				String statusMsg = "";

				SQLQuery historyQuery = (SQLQuery) session.createSQLQuery("insert into accomodationhistory (dormitoryid, dormitorybedid, studentid, accomodationstartintime, accomodationendouttime) values (?, ?, ?, ?, ?)");
				historyQuery.setInteger(0, studentbedlink.getDormitoryid());
				historyQuery.setInteger(1, studentbedlink.getDormitorybedid());
				historyQuery.setInteger(2, studentbedlink.getStudentid());
				historyQuery.setDate(3, studentbedlink.getStudentbedstartintime());
				historyQuery.setDate(4, new Date());
				//System.out.println("______________"+ReflectionToStringBuilder.toString(studentbedlink).toString());
				if (historyQuery.executeUpdate() >= 0) {
					statusMsg += "插入历史住宿信息成功。";
					log.info("DMS_info:插入历史住宿信息成功");
				} else {
					statusMsg += "插入历史住宿信息失败。";
					log.error("DMS_error:插入历史住宿信息失败");
				}

				String hqlDelete = "delete StudentBedLink s where s.studentid = :studentid";
				try {
					int result = session.createQuery(hqlDelete).setInteger("studentid", studentid).executeUpdate();
					if(result >= 0){
						statusMsg += "退宿成功";
						log.info("DMS_info:退宿成功");
						return statusMsg;
					}else{
						statusMsg += "退宿失败";
						log.info("DMS_info:退宿失败");
						return statusMsg;
					}
				} catch (Exception e) {
					statusMsg += "退宿失败";
					log.info("DMS_info:退宿失败");
					return statusMsg;
				}
			}
		});

	}

	public String insertAccomodationHistory(final int dormitoryid, final int dormitorybedid, final int studentid, final Date accomodationstartintime, final Date accomodationendouttime) {
		return (String) this.getHibernateTemplate().execute(new HibernateCallback() {
			public String doInHibernate(Session session) {
				SQLQuery historyQuery = (SQLQuery) session.createSQLQuery("insert into accomodationhistory (dormitoryid, dormitorybedid, studentid, accomodationstartintime, accomodationendouttime) values (?, ?, ?, ?, ?)");
				historyQuery.setInteger(0, dormitoryid);
				historyQuery.setInteger(1, dormitorybedid);
				historyQuery.setInteger(2, studentid);
				historyQuery.setDate(3, accomodationstartintime);
				historyQuery.setDate(4, accomodationendouttime);
				if (historyQuery.executeUpdate() > 0) {
					return "插入历史住宿信息成功。";
				} else {
					return "插入历史住宿信息失败。";
				}
			}
		});
	}

	@SuppressWarnings("unchecked")
	public List getAccomodationHistory(final String studentid) {
		return (List) this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public List doInHibernate(Session session) {
				SQLQuery query = (SQLQuery) session.createSQLQuery("select a.accomodationstartintime, a.accomodationendouttime, d.dormitorydisplayname from accomodationhistory a join dormitory d on a.dormitoryid=d.dormitoryid where studentid=? order by a.accomodationstartintime");
				query.setInteger(0, Integer.parseInt(studentid));

				return query.list();
			}
		});
	}

	/**
	 * to be modified!! --kk Description: 根据学生学号查询床位分配信息
	 * 
	 * @Version1.0 2009-3-26 00:28:50 闫克 （kurtyan777@gmail.com） 创建
	 * @param studentno
	 * @return StudentBedLink
	 * @throws DaoException
	 */
	@SuppressWarnings("unchecked")
	public List<StudentBedLink> getStudentBedLinkByStudentNo(String studentno) throws DaoException {
		// 应当先判断list.size()
		Student student = (Student) this.find("from Student where studentno = ?", new Object[] { studentno }).get(0);
		return this.find("from StudentBedLink where studentid = ?", new Object[] { student.getStudentid() });

	}

	/**
	 * 
	 * Description: 根据学生id 查询床位分配信息
	 * 
	 * @Version1.0 2009-3-26 01:18:50 闫克 （kurtyan777@gmail.com） 创建
	 * @param studentid
	 * @return StudentBedLink
	 * @throws DaoException
	 */
	@SuppressWarnings("unchecked")
	public List<StudentBedLink> getStudentBedLinkByStudentId(int studentid) throws DaoException {
		return this.find("from StudentBedLink where studentid = ?", new Object[] { studentid });

	}

	/**
	 * 
	 * Description: 根据宿舍号查询床位分配信息
	 * 
	 * @Version1.0 2009-3-26 00:36:28 闫克 （kurtyan777@gmail.com） 创建
	 * @param dormitoryname
	 * @return StudentBedLink
	 * @throws DaoException
	 */
	@SuppressWarnings("unchecked")
	public List<StudentBedLink> getStudentBedLinkByDormitoryName(String dormitoryname) throws DaoException {
		Dormitory dorm = (Dormitory) this.find("from Dormitory where dormitoryname= ?", new Object[] { dormitoryname }).get(0);
		return this.find("from StudentBedLink where dormitoryid= ?", new Object[] { dorm.getDormitoryid() });
	}

	/**
	 * Description: 根据sql语句进行批量退宿
	 * 
	 * @Version1.0 2009-4-5 上午 00:13:59 闫克 （kurtyan777@gmail.com） 创建 修改by 郑旭
	 * @param sql
	 * @return
	 */
	public String deleteBatchStudentBedLink(final String sql) {
		// final int resultCount = sql.substring(sql.indexOf("(") + 1,
		// sql.lastIndexOf(")")).split(",").length;
		return (String) this.getHibernateTemplate().execute(new HibernateCallback() {
			public String doInHibernate(Session session) {
				System.out.println("sql-------" +sql );
				SQLQuery query = (SQLQuery) session.createSQLQuery("insert into accomodationhistory (dormitoryid, dormitorybedid, studentid, accomodationstartintime, accomodationendouttime) select studentbedlink.dormitoryid as domirotyid, studentbedlink.dormitorybedid as dormitorybedid, studentbedlink.studentid as studentid, studentbedlink.studentbedstartintime as accomodationstartintime, NOW() from studentbedlink join student on studentbedlink.studentid=student.studentid " + sql);
				int resultCount = query.executeUpdate();
				if(resultCount>=0){
					log.info("DMS_info:插入" + resultCount + "名学生批量历史住宿信息成功");
				}else{
					log.info("DMS_info:插入" + resultCount + "名学生批量历史住宿信息失败");
				}
				query = (SQLQuery) session.createSQLQuery("delete studentbedlink from studentbedlink join student on studentbedlink.studentid=student.studentid " + sql);
				try {
					resultCount = query.executeUpdate();
					if(resultCount >= 0 ){
						log.info("DMS_info:" + resultCount + "名学生批量退宿成功");
						return resultCount + "";
					}else{
						return 0 + "";
					}
					
				} catch (HibernateException e) {
					e.printStackTrace();
					return 0 + "";
				}

			}
		});
	}

	/**
	 * Description: 根据学号查询住宿时间
	 * 
	 * @Version1.0 2009-4-18 21:06 闫克 （kurtyan777@gmail.com） 创建
	 * 
	 * @param studentno
	 * @return including studentid, studentname, studentno,
	 *         studentbedstartintime, studentbedendouttime,
	 *         dormitorydisplayname, dormitoryid
	 */
	@SuppressWarnings("unchecked")
	public List getLodgeTimeByStudentNo(final String studentno) {
		return (List) this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public List doInHibernate(Session session) {
				SQLQuery query = (SQLQuery) session.createSQLQuery("select s.studentid, s.studentname, s.studentno, sb.studentbedstartintime, sb.studentbedendouttime, d.dormitorydisplayname, d.dormitoryid from student s join studentbedlink sb on s.studentid=sb.studentid join dormitory d on d.dormitoryid=sb.dormitoryid where s.studentno=?");
				query.setString(0, studentno);

				return query.list();
			}
		});
	}

	public String updateStudentLodgeTime(String studentid, Date startintime, Date endouttime) {
		String statusMsg = "";
		StudentBedLink studentbedlink;
		try {
			studentbedlink = (StudentBedLink) this.find("from StudentBedLink where studentid = ?", new Object[] { Integer.parseInt(studentid) }).get(0);
		} catch (DaoException e1) {
			statusMsg = "延长住宿时间失败";
			log.error("DMS_error:延长住宿时间失败, 查询学生信息失败");
			return statusMsg;
		}

		studentbedlink.setStudentbedstartintime(startintime);
		studentbedlink.setStudentbedendouttime(endouttime);

		try {
			this.update(studentbedlink);
			statusMsg = "更改住宿时间成功";
			log.info("DMS_info:更新学生id为" + studentid + "住宿时间成功");
			return statusMsg;
		} catch (DaoException e) {
			// e.printStackTrace();
			log.info("DMS_info:更新学生id为" + studentid + "住宿时间失败");
			statusMsg = "更改住宿时间失败";
			return statusMsg;
		}

	}

	/**
	 * 
	 * Description:根据dormid去查询该宿舍的可住人数
	 * 
	 * @Version1.0 May 18, 2009 4:52:06 PM 李双江（paradise.lsj@gmail.com）创建
	 * @param dormid
	 * @return
	 */
	public int getAvailInDormAmountByDormId(String dormid) {
		SQLQuery query = (SQLQuery) this.getSession().createSQLQuery("select count(*) from studentbedlink where dormitoryid = " + dormid);
		if (query.list() != null && query.list().size() > 0) {
			return Integer.parseInt((query.list().get(0) + ""));
		}
		return -1;
	}
	public List getPreassignDateByPreID(int id){
		SQLQuery query = (SQLQuery) this.getSession().createSQLQuery("select starttime,endtime from allocationinfo where allocationid = " + id);
		if (query.list() != null && query.list().size() > 0) {
			return query.list();
		}
		return null;
	}

}
